#!/usr/bin/python
from akshare import stock_zh_index_daily
import pymysql
import numpy as np
import datetime
import sys
syspath=sys.path
sys.path.append("C:\\Users\\wx-5421\\PycharmProjects\\future1\\bean")
sys.path.append("C:\\Users\\wx-5421\\AppData\\Roaming\\Python\\Python37\\site-packages")
"""
策略：跟踪沪深300，消费，科技，医药，创业，新能源车，新能源的指数，统计macd小于0的天数，计算价格在近100天的位置.计算macd > 0和小于0的积分，计算比例，比例越小越需要关注。
计算macd大于0和小于0的积分，求积分的比例
sz000932 中证消费
sz000933 中证医药
sz399006 创业版指
sh000300 沪深300
sz399417 新能源车
sz980017  半导体
"""







class MySqlOperation():

    def __init__(self,host,user,passwd,db,port,chartset='utf-8'):
        try:
            self.con = pymysql.connect(host=host,user=user,passwd=passwd,port=port,db=db)
            self.con.autocommit(True)
        except BaseException as e:
            print(e)
            exit(1)

    #exec sql
    def executeSql(self,sql,args=None):
        try:
            cursor = self.con.cursor()
            cursor.execute(sql)
        except Exception as e:
            print(e)

    #query one data
    def queryOneData(self,sql,args=None):
        try:
            cursor = self.con.cursor()
            cursor.execute(sql,args)
            return cursor.fetchone()
        except Exception as e:
            print(e)

    #query many data
    def queryManyData(self,sql,args=None):
        try:
            cursor = self.con.cursor()
            cursor.execute(sql,args)
            return cursor.fetchall()
        except Exception as e:
            print(e)
    #close mysql
    def close(self):
        self.con.close()


class Index(object):



    #初始化函数
    def __init__(self,index_code):
        self.index_code = index_code

    #获取指数价格
    def get_price(self):
        index_price = stock_zh_index_daily(self.index_code)[-100:]
        return index_price

    def compute_macd(self,index_price, short_, long_, m):
        """
        data是包含高开低收成交量的标准dataframe
        short_,long_,m分别是macd的三个参数
        返回值是包含原始数据和diff,dea,macd三个列的dataframe
        :param price:
        :return:
        """
        index_price['diff'] = index_price['close'].ewm(adjust=False, alpha=2 / (short_ + 1), ignore_na=True).mean() - \
                       index_price['close'].ewm(adjust=False, alpha=2 / (long_ + 1), ignore_na=True).mean()
        index_price['dea'] = index_price['diff'].ewm(adjust=False, alpha=2 / (m + 1), ignore_na=True).mean()
        index_price['macd'] = 2 * (index_price['diff'] - index_price['dea'])
        diff = index_price['diff'].values
        dea = index_price['dea']
        macd = index_price['macd'].values
        return macd,dea,diff

    #获取指数macd
    def get_macd(self,index_price):
        pass

    #获取指数过去一段时间内macd值大于0和小于0的比例
    def get_ratio(self,signal_value):
        signal_positive = signal_value[signal_value > 0].count()
        signal_negative = signal_value[signal_value < 0].count()
        if signal_negative != 0:
            signal_rate = signal_positive / signal_negative
        else:
            signal_rate = 0
        return signal_rate

    #判断指数是不是金叉
    def get_golden_cross(self,macd_value):
        first_macd = macd_value[-1]
        second_macd = macd_value[-3]
        if (first_macd > 0) and (second_macd < 0):
            print("该指数出现金叉，可以考虑买入。")
        else:
            print("请继续等待买入机会。")

    #计算指数处于最近100天的分位数
    def get_percentage(self,index_price):
        index_price_close = index_price['close']
        index_price_close_max = index_price_close.max()
        index_price_close_min = index_price_close.min()
        index_price_close_last = index_price_close[-1]
        index_price_rate = (index_price_close_last - index_price_close_min) / (
                    index_price_close_max - index_price_close_min)
        return index_price_rate

    def insert_database(self,host_ip,user,passwd,db,sql):
        mysql = MySqlOperation(host=host_ip, user=user, passwd=passwd, db=db, port=3306)
        mysql.executeSql(sql)
        mysql.close()

index_dict = {"中证消费":"sz000932",
             "中证医药":"sz000933",
             "创业版":"sz399006",
             "沪深300" :"sh000300",
              "新能源车":"sz399417",
              "半导体":"sz980017"
             }

host_ip = "10.10.134.58"
user = "root"
passwd = "Yanshi@123"
db = "index"
date = datetime.date.today() - datetime.timedelta(days=1)
print(date)

for index_name,index_code in index_dict.items():
    index = Index(index_code)
    index_price = index.get_price()
    macd,signal,hist = index.compute_macd(index_price,12,26,9)
    signal_rate = np.round(index.get_ratio(signal),2)
    percentage = np.round(index.get_percentage(index_price),2)
    # #插入数据库,字段signal_rate，percentage
    if percentage < 0.23 or signal_rate < 0.625:
        print("指数名称: "+index_name)
        index.get_golden_cross(macd)
    else:
        print("指数名称: " + index_name)
        print("价位不够低，谨慎考虑，容易追高。")

    #插入数据库
    sql = "insert into index.index (index_code,index_name,ratio,percentage,date) values ('{index_code}','{index_name}',{ratio},{percentage},'{date}')".format(index_code=index_code,index_name=index_name,ratio=signal_rate,percentage=percentage,date=date)
    print(sql)
    index.insert_database(host_ip=host_ip,user=user,passwd=passwd,db=db,sql=sql)

